clear

cd "C:\Users\maschaff\OneDrive\Documents\Research\banking\April 2019"

*Load Data
use "data_geo.dta", clear

*****************************************************************************
*** Setup state-level data ***

drop if rssd9200 == "0"
keep if  rssd9210>0 & rssd9210< 57

keep if year_q < tq(1995q1)

gen loans = rcfd1400
replace loans = rcfd1400+rcfd2165 if year_q < tq(1984q1)

gen c_p_i = cpi/100
gen r_loans = loans/c_p_i 
gen r_ci = rcfd1600/c_p_i
gen r_rlest = rcfd1410/c_p_i
gen r_con = rcfd1975/c_p_i
gen r_ag = rcfd1590/c_p_i

gen Quarter = quarter(date2)
gen Year = year(date2) 

gen bhc = 0
replace bhc = 1 if rssd9379 != 0

sort year_q
by year_q: egen p95 = pctile(rcfd2170), p(95)
by year_q: egen p99 = pctile(rcfd2170), p(99)

by year_q: egen natl_loans = sum(r_loans)
by year_q: egen sa_small_natl = sum(r_loans) if rcfd2170 < p95 & bhc==0
by year_q: egen sa_big_natl = sum(r_loans) if rcfd2170 >= p95 & bhc == 0
by year_q: egen bhc_small_natl = sum(r_loans) if rcfd2170 < p95 & bhc==1
by year_q: egen bhc_big_natl = sum(r_loans) if rcfd2170 >= p95 & bhc==1

sort rssd9210 year_q

by rssd9210 year_q: egen s50 = pctile(rcfd2170), p(50)
by rssd9210 year_q: egen s80 = pctile(rcfd2170), p(80)
by rssd9210 year_q: egen s95 = pctile(rcfd2170), p(95)
by rssd9210 year_q: egen s99 = pctile(rcfd2170), p(95)
by rssd9210 year_q: egen sliq50 = pctile(Liq), p(50)
by rssd9210 year_q: egen sx50 = pctile(ineff), p(50)

by rssd9210 year_q: egen sa_small_loans = sum(r_loans) if rcfd2170 < p95 & bhc==0
by rssd9210 year_q: egen sa_big_loans = sum(r_loans) if rcfd2170 >= p95 & bhc == 0
by rssd9210 year_q: egen bhc_small_loans = sum(r_loans) if rcfd2170 < p95 & bhc==1
by rssd9210 year_q: egen bhc_big_loans = sum(r_loans) if rcfd2170 >= p95 & bhc==1

by rssd9210 year_q: egen agg_loans = sum(r_loans)

replace intra = 1 if intra == .
replace inter =1 if inter == .

collapse (mean) agg_loans sa_small_loans sa_big_loans bhc_small_loans bhc_big_loans natl_loans sa_small_natl sa_big_natl bhc_small_natl bhc_big_natl c_p_i pi_pchg gdp_chg pce_chg vwretx hpi_chg rr inter intra Quarter Year, by (rssd9210 year_q)

xtset rssd9210 year_q

edit sa_small_share2 sa_big_share2 bhc_small_share2 bhc_big_share2 Year

